package com.vimhe.easyimport.reader;

import com.vimhe.easyimport.util.HexUtils;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

/**
 * Excel2007表格解析处理驱动
 *
 * @author Vimhe
 */
public class Excel2007Handler extends DefaultHandler {

    /**
     * 存储真实数据的字符串表
     */
    private SharedStringsTable sst;

    /**
     * 存储单元格数据格式的样式表
     */
    private StylesTable stylesTable;

    /**
     * 用于格式化数据
     */
    private DataFormatter formatter = new DataFormatter();

    /**
     * 格式化方式
     */
    private short formatIndex;

    /**
     * 格式化字符串
     */
    private String formatString;

    /**
     * 当前解析到的工作表索引
     */
    private int sheetIndex;

    /**
     * 当前解析到的行索引
     */
    private int rowIndex;

    /**
     * 当前解析到的单元格索引
     */
    private String cellIndex;

    /**
     * 上一次解析到的列索引
     */
    private String lastCellIndex;

    /**
     * 当前解析到的单元格是否有数据
     */
    private boolean hasValue;

    /**
     * 当前解析到的单元格的数据类型
     */
    private DataType curDataType;

    /**
     * 用于存储当前解析到的单元格的数据
     */
    private StringBuffer cellValue = new StringBuffer();

    /**
     * 用于存储每行数据，单元格索引作为key，对应单元格的数据作为value
     */
    private Map<String, Object> rowValue = new HashMap<>(16);

    /**
     * 用于存储每个工作表的数据，行索引作为key，对应行的数据作为value
     */
    private Map<Integer, Map<String, Object>> sheetValue;

    /**
     * 用于存储每个文件的数据，工作表索引作为key，对应工作表的数据作为value
     */
    private Map<Integer, Map<Integer, Map<String, Object>>> fileValue;

    /**
     * Excel数据读取器接口
     */
    private IExcelReader excelReader;

    public Excel2007Handler(IExcelReader excelReader) {
        this.excelReader = excelReader;
    }

    @Override
    public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
        // 工作表开始
        if ("worksheet".equals(name)) {
            rowIndex = 0;

            sheetValue = new HashMap<>(128);
        }
        // 一行开始
        else if ("row".equals(name)) {
            rowIndex++;
            cellIndex = "";
            lastCellIndex = "";

            rowValue = new HashMap<>(16);
        }
        // 单元格开始
        else if ("c".equals(name)) {
            // 单元格标识，如“A、A1、B2”
            String r = attributes.getValue("r");

            cellIndex = r.replaceAll("\\d+", "");

            // 设置数据类型
            String t = attributes.getValue("t");
            if ("b".equals(t)) {
                curDataType = DataType.BOOL;
            } else if ("e".equals(t)) {
                curDataType = DataType.ERROR;
            } else if ("inlineStr".equals(t)) {
                curDataType = DataType.INLINE_STR;
            } else if ("s".equals(t)) {
                curDataType = DataType.SST_INDEX;
            } else if ("str".equals(t)) {
                curDataType = DataType.FORMULA;
            } else {
                curDataType = DataType.NUMBER;
            }

            // 根据单元格格式取得格式化数据方式
            String s = attributes.getValue("s");
            if (s != null) {
                XSSFCellStyle style = stylesTable.getStyleAt(Integer.parseInt(s));
                formatIndex = style.getDataFormat();
                formatString = style.getDataFormatString();
                if (formatString == null) {
                    formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
                }
            }
        }
        // 单元格的值开始
        else if ("v".equals(name) || "inlineStr".equals(name)) {
            hasValue = true;
            cellValue.setLength(0);
        }
    }

    @Override
    public void endElement(String uri, String localName, String name) throws SAXException {
        // 单元格的值结束
        if ("v".equals(name)) {
            Object curValue;

            // 根据数据类型转换数据
            switch (curDataType) {
                case BOOL:
                    curValue = cellValue.charAt(0) == '0' ? "FALSE" : "TRUE";
                    break;
                case ERROR:
                    cellValue.insert(0, "\"ERROR:");
                    cellValue.insert(cellValue.length(), "\"");
                    curValue = cellValue.toString();
                    break;
                case INLINE_STR:
                    XSSFRichTextString rtsi = new XSSFRichTextString(cellValue.toString());
                    curValue = rtsi.toString();
                    break;
                case SST_INDEX:
                    String sstIndex = cellValue.toString();
                    try {
                        int idx = Integer.parseInt(sstIndex);
                        XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));
                        curValue = rtss.toString();
                    } catch (Exception e) {
                        curValue = sstIndex;
                    }
                    break;
                case FORMULA:
                    cellValue.insert(0, "\"");
                    cellValue.insert(cellValue.length(), "\"");
                    curValue = cellValue.toString();
                    break;
                case NUMBER:
                    String number = cellValue.toString();
                    if (formatString != null) {
                        curValue = formatter.formatRawCellContents(Double.parseDouble(number), formatIndex, formatString);
                    } else {
                        curValue = number;
                    }
                    break;
                default:
                    curValue = "";
                    break;
            }

            // 在同一行内，若后一次比前一次解析取的列序号相差大于1，证明中间没有解析到值
            // 按照.xlsx底层是xml描述文件原理，此时对应xml中“空值”的情况
            int curCellIndex = HexUtils.letter2Number(cellIndex);
            int lastCellIndexNum = HexUtils.letter2Number(lastCellIndex);
            if (curCellIndex - lastCellIndexNum > 1) {
                for (int i = lastCellIndexNum + 1; i < curCellIndex; i++) {
                    // 对当前单元格的值进行处理
                    String colName = HexUtils.number2Letter(i);
                    Object newValue = excelReader.readCell(sheetIndex, rowIndex, colName, "");

                    // 加入单元格的值到当前行
                    if (newValue != null) {
                        rowValue.put(colName, newValue);
                    }
                }
            }

            // 对当前单元格的值进行处理
            curValue = excelReader.readCell(sheetIndex, rowIndex, cellIndex, curValue);

            // 加入单元格的值到当前行
            if (curValue != null) {
                rowValue.put(cellIndex, curValue);
            }

            // 更新列索引计数
            lastCellIndex = cellIndex;
        }
        // 一行结束
        else if ("row".equals(name)) {
            int cellCount = excelReader.getCellCount(sheetIndex, rowIndex);
            int lastCellIndexNum = HexUtils.letter2Number(lastCellIndex);

            // 整行为空则忽略
            // 如果经过一行之后lastCellIndex的值仍然不变，证明该行是空行
            if (lastCellIndexNum > 0) {
                for (int i = lastCellIndexNum + 1; i <= cellCount; i++) {
                    // 对当前单元格的值进行处理
                    String colName = HexUtils.number2Letter(i);
                    Object newValue = excelReader.readCell(sheetIndex, rowIndex, colName, "");

                    // 加入单元格的值到当前行
                    if (newValue != null) {
                        rowValue.put(colName, newValue);
                    }
                }

                // 对当前行的值进行处理
                rowValue = excelReader.readRow(sheetIndex, rowIndex, rowValue);

                // 加入行数据到当前工作表
                if (rowValue != null) {
                    sheetValue.put(rowIndex, rowValue);
                }
            }
        }
        // 工作表结束
        else if ("worksheet".equals(name)) {
            sheetValue = excelReader.readSheet(sheetIndex, sheetValue);
        }
    }

    @Override
    public void characters(char[] ch, int start, int length) throws SAXException {
        // 仅当遇到“v”和“inlineStr”元素时才代表有值
        if (hasValue) {
            cellValue.append(ch, start, length);
        }
    }

    /**
     * 遍历工作簿中所有的电子表格
     *
     * @param filename 文件名
     * @throws IOException        e
     * @throws OpenXML4JException e
     * @throws SAXException       e
     */
    public void process(String filename) throws IOException, OpenXML4JException, SAXException {
        OPCPackage opcPackage = OPCPackage.open(filename);
        process(opcPackage);
    }

    /**
     * 遍历工作簿中所有的电子表格
     *
     * @param file 文件
     * @throws IOException        e
     * @throws OpenXML4JException e
     * @throws SAXException       e
     */
    public void process(File file) throws IOException, OpenXML4JException, SAXException {
        OPCPackage opcPackage = OPCPackage.open(file);
        process(opcPackage);
    }

    /**
     * 遍历工作簿中所有的电子表格
     *
     * @param inputStream 文件流
     * @throws IOException        e
     * @throws OpenXML4JException e
     * @throws SAXException       e
     */
    public void process(InputStream inputStream) throws IOException, OpenXML4JException, SAXException {
        OPCPackage opcPackage = OPCPackage.open(inputStream);
        process(opcPackage);
    }

    /**
     * 遍历工作簿中所有的电子表格
     *
     * @param opcPackage 文档信息
     * @throws IOException        e
     * @throws OpenXML4JException e
     * @throws SAXException       e
     */
    private void process(OPCPackage opcPackage) throws IOException, OpenXML4JException, SAXException {
        InputStream sheet = null;
        try {
            XSSFReader xssfReader = new XSSFReader(opcPackage);
            stylesTable = xssfReader.getStylesTable();
            SharedStringsTable stringsTable = xssfReader.getSharedStringsTable();
            XMLReader xmlReader = fetchSheetParser(stringsTable);
            XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) xssfReader.getSheetsData();

            sheetIndex = 0;
            fileValue = new HashMap<>(16);
            while (sheetIterator.hasNext()) {
                sheetIndex++;
                sheet = sheetIterator.next();
                InputSource sheetSource = new InputSource(sheet);
                xmlReader.parse(sheetSource);
                fileValue.put(sheetIndex, sheetValue);
            }
            excelReader.readFile(fileValue);
        } finally {
            if (sheet != null) {
                try {
                    sheet.close();
                } catch (IOException ignored) {
                }
            }
        }
    }

    /**
     * 获取解析器
     *
     * @param sst 存储真实数据的字符串表
     * @return 解析器
     * @throws SAXException e
     */
    private XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
        this.sst = sst;

        XMLReader parser = XMLReaderFactory.createXMLReader();
        parser.setContentHandler(this);
        return parser;
    }

    /**
     * 单元格中的数据可能的数据类型
     */
    private enum DataType {
        // 布尔，错误，公式，字符串，索引，数字
        BOOL, ERROR, FORMULA, INLINE_STR, SST_INDEX, NUMBER
    }

}
